<?php

 
 Zend_Loader::loadClass("BaseDBRows");
 Zend_Loader::loadClass("Shipment");
 class Shipments extends BaseDBRows
 {
 	protected function setUp()
 	{
 		$this->_tableName = Shipment::_tablename;
 	}	
	
	public function getListOfShipments($org_id,$param){
	
		//print_r($param);
	
		Zend_Loader::loadClass("Shipmethod");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("purchaseOrder");
		Zend_Loader::loadClass("Address");
		
		$query = new QueryCreator();
		
		$query->addSelect(" po.po_no,org.name,spm.name as carrier_type,spm.description as carrier_name,shipment.carrier_code,shipment.date_shipment,shipment.shipment_id ");
		$query->addFrom( $this->_tableName . " shipment " );
		$query->addJoin(" LEFT JOIN ". Organization::_tablename . " org on shipment.customer_org_id = org.org_id ");
		$query->addJoin(" LEFT JOIN ". Shipmethod::_tablename . " spm on spm.ship_method_id = shipment.ship_method_id ");
		$query->addJoin(" LEFT JOIN ". purchaseOrder::_tablename . " po on po.po_id = shipment.po_id ");
		$query->addJoin(" LEFT JOIN ". Address::_tablename ." address on address.address_id = org.primary_address_id");
		$query->addWhere(" shipment.org_id  = ". $org_id );
		
		if(strlen($param['country']) > 0)
			$query->addWhere(" address.country_code = '" . $param['country'] . "'");
			
		if(strlen($param['customer']) > 0)
			$query->addWhere(" shipment.customer_org_id  = ". $param['customer']);
			
		if(strlen($param['date_from']) > 0 && strlen($param['date_to']) > 0)	
			$query->addWhere(" date_shipment between '". $param['date_from'] . "' and '". $param['date_to'] . "'" );
		
		$db = DBCon::instance();
		
		$result = $db->executeQuery($query->createSQL());
		
		return $result;
		
	}
	
	public function getMonthlyListOfShipmentSummary($orgType, $country="", $province="", $orgId=0, $dateFrom="", $dateTo="") {
		
		Zend_Loader::loadClass("ShipmentDetail");
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("OrganizationAddress");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("QueryCreator");

		$query = new QueryCreator();
		$query->addSelect("date_format(s.date_shipment,'%Y%m') yearmonth, sum(sd.ship_quantity) sum_ship_quantity");
		$query->addSelect("count(distinct od.org_organism_id) count_species_delivered, sum(sd.ship_amount) sum_ship_amount, sd.currency_mark");
		$query->addFrom($this->_tableName . " s ");
		$query->addJoin(", " . ShipmentDetail::_tablename . " sd ");
		$query->addJoin(", " . purchaseOrderDetail::_tablename . " od ");
		$query->addJoin(", " . Organization::_tablename . " org ");
		$query->addJoin(" left join " . OrganizationAddress::_tablename . " oa on org.primary_address_id = oa.org_address_id and org.org_id = oa.org_id");
		$query->addJoin(" left join " . Address::_tablename . " a on oa.address_id = a.address_id");
		$query->addWhere("s.shipment_id = sd.shipment_id");
		$query->addWhere("s.po_id = od.po_id");
		$query->addWhere("sd.po_line_no = od.po_line_no");
		$query->addwhere("s.org_id = org.org_id");
		$query->addWhere("org.org_type = '" . $orgType . "' ");
		$query->addGroupBy(" date_format(s.date_shipment,'%Y%m'), sd.currency_mark ");
		
		// country
		if(strlen($country) > 0) {
			$query->addWhere("a.country_code = '" . $country . "' ");
		}
		
		// province
		if(strlen($province) > 0) {
			$query->addWhere("a.province = '" . $province . "' ");
		}
		
		// org 
		if(strlen($orgId) > 0 && $orgId > 0) {
			$query->addWhere("org.org_id = " . $orgId);
		}

		// dates from - to
		if(strlen($dateFrom) > 0 && strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment between least('" . $dateFrom . "','" . $dateTo . "') and greatest('" . $dateFrom . "','" . $dateTo . "')" );	
		}
		elseif(strlen($dateFrom) > 0) {
			$query->addWhere(" s.date_shipment >= '" . $dateFrom . "'" );
		}
		elseif(strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment <= '" . $dateTo . "'");
		}

		$db = DBCon::instance();
		// echo  $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		return $result;

	}

	public function getMonthlyListOfShipmentDetail($orgType, $country="", $province="", $orgId=0, $dateFrom="", $dateTo="") {
		
		Zend_Loader::loadClass("ShipmentDetail");
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("OrganizationAddress");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("QueryCreator");

		$query = new QueryCreator();
		$query->addSelect("org.org_id, org.name, date_format(s.date_shipment,'%Y%m') yearmonth");
		$query->addSelect("count(sd.shipment_id) count_shipments, sum(sd.ship_quantity) sum_ship_quantity");
		$query->addFrom($this->_tableName . " s ");
		$query->addJoin(", " . ShipmentDetail::_tablename . " sd ");
		$query->addJoin(", " . purchaseOrderDetail::_tablename . " od ");
		$query->addJoin(", " . Organization::_tablename . " org ");
		$query->addJoin(" left join " . OrganizationAddress::_tablename . " oa on org.primary_address_id = oa.org_address_id and org.org_id = oa.org_id");
		$query->addJoin(" left join " . Address::_tablename . " a on oa.address_id = a.address_id");
		$query->addWhere("s.shipment_id = sd.shipment_id");
		$query->addWhere("s.po_id = od.po_id");
		$query->addWhere("sd.po_line_no = od.po_line_no");
		$query->addwhere("s.org_id = org.org_id");
		$query->addWhere("org.org_type = '" . $orgType . "' ");
		$query->addGroupBy(" date_format(s.date_shipment,'%Y%m'), org.org_id, org.name");
		$query->addOrderBy("org.name");

		// country
		if(strlen($country) > 0) {
			$query->addWhere("a.country_code = '" . $country . "' ");
		}
		
		// province
		if(strlen($province) > 0) {
			$query->addWhere("a.province = '" . $province . "' ");
		}
		
		// org 
		if(strlen($orgId) > 0 && $orgId > 0) {
			$query->addWhere("org.org_id = " . $orgId);
		}

		// dates from - to
		if(strlen($dateFrom) > 0 && strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment between least('" . $dateFrom . "','" . $dateTo . "') and greatest('" . $dateFrom . "','" . $dateTo . "')" );	
		}
		elseif(strlen($dateFrom) > 0) {
			$query->addWhere(" s.date_shipment >= '" . $dateFrom . "'" );
		}
		elseif(strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment <= '" . $dateTo . "'");
		}

		$db = DBCon::instance();
		// echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;

	}
	
	public function getMonthlyCountOfSupplierDetail($orgType, $country="", $province="", $orgId=0, $dateFrom="", $dateTo="") {
		
		Zend_Loader::loadClass("ShipmentDetail");
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("OrganizationAddress");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("QueryCreator");

		$query = new QueryCreator();
		$query->addSelect("org.org_id, org.name");
		$query->addFrom($this->_tableName . " s ");
		$query->addJoin(", " . ShipmentDetail::_tablename . " sd ");
		$query->addJoin(", " . purchaseOrderDetail::_tablename . " od ");
		$query->addJoin(", " . Organization::_tablename . " org ");
		$query->addJoin(" left join " . OrganizationAddress::_tablename . " oa on org.primary_address_id = oa.org_address_id and org.org_id = oa.org_id");
		$query->addJoin(" left join " . Address::_tablename . " a on oa.address_id = a.address_id");
		$query->addWhere("s.shipment_id = sd.shipment_id");
		$query->addWhere("s.po_id = od.po_id");
		$query->addWhere("sd.po_line_no = od.po_line_no");
		$query->addwhere("s.org_id = org.org_id");
		$query->addWhere("org.org_type = '" . $orgType . "' ");
		$query->addGroupBy("org.org_id, org.name");
		$query->addOrderBy("org.name");

		// country
		if(strlen($country) > 0) {
			$query->addWhere("a.country_code = '" . $country . "' ");
		}
		
		// province
		if(strlen($province) > 0) {
			$query->addWhere("a.province = '" . $province . "' ");
		}
		
		// org 
		if(strlen($orgId) > 0 && $orgId > 0) {
			$query->addWhere("org.org_id = " . $orgId);
		}

		// dates from - to
		if(strlen($dateFrom) > 0 && strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment between least('" . $dateFrom . "','" . $dateTo . "') and greatest('" . $dateFrom . "','" . $dateTo . "')" );	
		}
		elseif(strlen($dateFrom) > 0) {
			$query->addWhere(" s.date_shipment >= '" . $dateFrom . "'" );
		}
		elseif(strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment <= '" . $dateTo . "'");
		}

		$db = DBCon::instance();
		// echo  $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		return $result;

	}

	public function getMonthlySumOfShipmentDetail($orgType, $country="", $province="", $orgId=0, $dateFrom="", $dateTo="") {
		
		Zend_Loader::loadClass("ShipmentDetail");
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("OrganizationAddress");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("QueryCreator");

		$query = new QueryCreator();
		$query->addSelect("date_format(s.date_shipment,'%Y%m') yearmonth");
		$query->addSelect("count(sd.shipment_id) count_shipments, sum(sd.ship_quantity) sum_ship_quantity");
		$query->addFrom($this->_tableName . " s ");
		$query->addJoin(", " . ShipmentDetail::_tablename . " sd ");
		$query->addJoin(", " . purchaseOrderDetail::_tablename . " od ");
		$query->addJoin(", " . Organization::_tablename . " org ");
		$query->addJoin(" left join " . OrganizationAddress::_tablename . " oa on org.primary_address_id = oa.org_address_id and org.org_id = oa.org_id");
		$query->addJoin(" left join " . Address::_tablename . " a on oa.address_id = a.address_id");
		$query->addWhere("s.shipment_id = sd.shipment_id");
		$query->addWhere("s.po_id = od.po_id");
		$query->addWhere("sd.po_line_no = od.po_line_no");
		$query->addwhere("s.org_id = org.org_id");
		$query->addWhere("org.org_type = '" . $orgType . "' ");
		$query->addGroupBy("date_format(s.date_shipment,'%Y%m')");
		$query->addOrderBy("date_format(s.date_shipment,'%Y%m')");

		// country
		if(strlen($country) > 0) {
			$query->addWhere("a.country_code = '" . $country . "' ");
		}
		
		// province
		if(strlen($province) > 0) {
			$query->addWhere("a.province = '" . $province . "' ");
		}
		
		// org 
		if(strlen($orgId) > 0 && $orgId > 0) {
			$query->addWhere("org.org_id = " . $orgId);
		}

		// dates from - to
		if(strlen($dateFrom) > 0 && strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment between least('" . $dateFrom . "','" . $dateTo . "') and greatest('" . $dateFrom . "','" . $dateTo . "')" );	
		}
		elseif(strlen($dateFrom) > 0) {
			$query->addWhere(" s.date_shipment >= '" . $dateFrom . "'" );
		}
		elseif(strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment <= '" . $dateTo . "'");
		}

		$db = DBCon::instance();
		// echo  $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		return $result;

	}

	public function getShipmentTrackingSummary($orgType, $country="", $province="", $orgId=0, $dateFrom="", $dateTo="") {
	
		Zend_Loader::loadClass("ShipmentDetail");
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("purchaseOrder");
		Zend_Loader::loadClass("Receipt");
		Zend_Loader::loadClass("ReceiptReject");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("OrganizationAddress");
		Zend_Loader::loadClass("Address");
		
		$query = new QueryCreator();
		$query->addSelect("orgexp.id exp_org_id, orgexp.name exp_name, s.date_shipment, sum(sd.ship_quantity) sum_shipment");
		$query->addSelect("sum(r.doa_quantity) sum_doa, sum(r.reject_quantity) sum_reject");
		$query->addSelect("sum(s.total_amount) sum_total_amount, s.currency_mark");
		$query->addFrom($this->_tableName . " s ");
		$query->addJoin(", " . ShipmentDetail::_tablename . " sd ");
		$query->addJoin(", " . purchaseOrderDetail::_tablename . " od ");
		$query->addJoin(", " . purchaseOrder::_tablename . " o ");
		$query->addJoin(", (select r.org_id, r.reference_id, ifnull(sum(r.doa_quantity),0) doa_quantity, ifnull(sum(rr.reject_quantity),0) reject_quantity from " . Receipt::_tablename . " r left join " . ReceiptReject::_tablename . " rr on r.receipt_id = rr.receipt_id group by r.org_id, r.reference_id) r ");
		$query->addJoin(", " . Organization::_tablename . " orgexp ");
		$query->addJoin(" left join " . OrganizationAddress::_tablename . " orgadd on orgexp.org_id = orgadd.org_id and orgexp.primary_address_id = orgadd.org_address_id ");
		$query->addJoin(" left join " . Address::_tablename . " ad on orgadd.address_id = ad.address_id ");
		$query->addWhere(" s.shipment_id = sd.shipment_id ");
		$query->addWhere(" s.po_id = od.po_id ");
		$query->addWhere(" sd.po_line_no = od.po_line_no ");
		$query->addWhere(" od.po_id = o.po_id ");
		$query->addWhere(" s.po_id = r.reference_id ");
		$query->addWhere(" s.org_id = orgexp.org_id ");
		$query->addWhere(" orgexp.org_type = '" . $orgType . "' ");
		$query->addGroupBy(" orgexp.id, orgexp.name, s.date_shipment, s.currency_mark ");
		$query->addOrderBy(" orgexp.name, date_shipment ");
		
		// country
		if(strlen($country) > 0) {
			$query->addWhere("ad.country_code = '" . $country . "' ");
		}
		
		// province
		if(strlen($province) > 0) {
			$query->addWhere("ad.province = '" . $province . "' ");
		}
		
		// org id
		if(isset($orgId) && $orgId > 0) {
			$query->addWhere(" orgexp.org_id = " . $orgId);
		}

		// dates from - to
		if(strlen($dateFrom) > 0 && strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment between least('" . $dateFrom . "','" . $dateTo . "') and greatest('" . $dateFrom . "','" . $dateTo . "')" );	
		}
		elseif(strlen($dateFrom) > 0) {
			$query->addWhere(" s.date_shipment >= '" . $dateFrom . "'" );
		}
		elseif(strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment <= '" . $dateTo . "'");
		}
		// print_r($query);die();
		$db = DBCon::instance();
		// echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;
	
	}

	public function getOrgShipmentDates($orgType, $orgId=0) {

		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");

		$query = new QueryCreator();
		$query->addSelect("s.org_id, s.date_shipment");
		$query->addFrom($this->_tableName . " s ");
		$query->addJoin(", " . Organization::_tablename . " o ");
		$query->addWhere(" s.org_id = o.org_id ");
		$query->addWhere(" o.org_type = '" . $orgType . "' ");
		$query->addGroupBy("org_id, date_shipment");
		$query->addOrderBy("org_id, date_shipment");

		// org id
		if(isset($orgId) && $orgId > 0) {
			$query->addWhere(" o.org_id = " . $orgId);
		}

		
		$db = DBCon::instance();
		// echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;

	}

	public function getShipmentExporters($orgType, $orgId=0, $position=0,$limit=1, $country="", $province="", $dateFrom="", $dateTo="") {
	
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("OrganizationAddress");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("QueryCreator");

		$query = new QueryCreator();
		$query->addSelect(" distinctrow org.*, ad.* ");
		$query->addFrom($this->_tableName . " s ");
		$query->addJoin(", " . Organization::_tablename . " org ");
		$query->addJoin(" left join " . OrganizationAddress::_tablename . " orgadd on org.org_id = orgadd.org_id and org.primary_address_id = orgadd.org_address_id ");
		$query->addJoin(" left join " . Address::_tablename . " ad on orgadd.address_id = ad.address_id ");
		$query->addWhere(" s.org_id = org.org_id ");
		$query->addWhere(" org.org_type = '" . $orgType . "' ");
		$query->addOrderBy(" org.name ");

		// country
		if(strlen($country) > 0) {
			$query->addWhere("ad.country_code = '" . $country . "' ");
		}
		
		// province
		if(strlen($province) > 0) {
			$query->addWhere("ad.province = '" . $province . "' ");
		}
		
		// org id
		if(isset($orgId) && $orgId > 0) {
			$query->addWhere(" org.org_id = " . $orgId);
		}

		// dates from - to
		if(strlen($dateFrom) > 0 && strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment between least('" . $dateFrom . "','" . $dateTo . "') and greatest('" . $dateFrom . "','" . $dateTo . "')" );	
		}
		elseif(strlen($dateFrom) > 0) {
			$query->addWhere(" s.date_shipment >= '" . $dateFrom . "'" );
		}
		elseif(strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment <= '" . $dateTo . "'");
		}

		// limit: if 0 return all rows
		if(isset($limit) && $limit > 0) {
			$query->addLimit($position . ", " . $limit);		
		}
		//print_r($query);die();
		$db = DBCon::instance();
		// echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;

	}

	public function getOrgShipmentOrgs($orgType, $orgId=0, $country="", $province="", $dateFrom="", $dateTo="") {
		
		Zend_Loader::loadClass("purchaseOrder");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("OrganizationAddress");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("QueryCreator");

		$query = new QueryCreator();
		$query->addSelect("s.org_id exporter, date_shipment, org.id,org.name");
		$query->addFrom($this->_tableName . " s ");
		$query->addJoin(", " . purchaseOrder::_tablename . " o ");
		$query->addJoin(", " . Organization::_tablename . " org ");
		$query->addJoin(" left join " . OrganizationAddress::_tablename . " orgadd on org.org_id = orgadd.org_id and org.primary_address_id = orgadd.org_address_id ");
		$query->addJoin(" left join " . Address::_tablename . " ad on orgadd.address_id = ad.address_id ");
		$query->addWhere("s.po_id = o.po_id");
		$query->addWhere("o.org_id = org.org_id");
		$query->addOrderBy("date_shipment, org.name ");

		// country
		if(strlen($country) > 0) {
			$query->addWhere("ad.country_code = '" . $country . "' ");
		}
		
		// province
		if(strlen($province) > 0) {
			$query->addWhere("ad.province = '" . $province . "' ");
		}
		
		// org id
		if(isset($orgId) && $orgId > 0) {
			$query->addWhere(" s.org_id = " . $orgId);
		}

		// dates from - to
		if(strlen($dateFrom) > 0 && strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment between least('" . $dateFrom . "','" . $dateTo . "') and greatest('" . $dateFrom . "','" . $dateTo . "')" );	
		}
		elseif(strlen($dateFrom) > 0) {
			$query->addWhere(" s.date_shipment >= '" . $dateFrom . "'" );
		}
		elseif(strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment <= '" . $dateTo . "'");
		}
		
		$db = DBCon::instance();
		//echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;

	}

	public function getCountOrgShipmentOrgs($orgType, $orgId=0, $country="", $province="", $dateFrom="", $dateTo="") {
		
		Zend_Loader::loadClass("purchaseOrder");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("OrganizationAddress");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("QueryCreator");

		$query = new QueryCreator();
		$query->addSelect("distinctrow org.id,org.name");
		$query->addFrom($this->_tableName . " s ");
		$query->addJoin(", " . purchaseOrder::_tablename . " o ");
		$query->addJoin(", " . Organization::_tablename . " org ");
		$query->addJoin(" left join " . OrganizationAddress::_tablename . " orgadd on org.org_id = orgadd.org_id and org.primary_address_id = orgadd.org_address_id ");
		$query->addJoin(" left join " . Address::_tablename . " ad on orgadd.address_id = ad.address_id ");
		$query->addWhere("s.po_id = o.po_id");
		$query->addWhere("o.org_id = org.org_id");
		$query->addOrderBy("date_shipment, org.name ");

		// country
		if(strlen($country) > 0) {
			$query->addWhere("ad.country_code = '" . $country . "' ");
		}
		
		// province
		if(strlen($province) > 0) {
			$query->addWhere("ad.province = '" . $province . "' ");
		}
		
		// org id
		if(isset($orgId) && $orgId > 0) {
			$query->addWhere(" s.org_id = " . $orgId);
		}

		// dates from - to
		if(strlen($dateFrom) > 0 && strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment between least('" . $dateFrom . "','" . $dateTo . "') and greatest('" . $dateFrom . "','" . $dateTo . "')" );	
		}
		elseif(strlen($dateFrom) > 0) {
			$query->addWhere(" s.date_shipment >= '" . $dateFrom . "'" );
		}
		elseif(strlen($dateTo) > 0) {
			$query->addWhere(" s.date_shipment <= '" . $dateTo . "'");
		}
		
		$db = DBCon::instance();
		//echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;

	}

 }
?>